
clear
set obs 1
gen blank =1
save ${nc_raw_data}/temp4, replace

forvalues t=2006/2016 {
	disp "************`t'***********"
	local f = "exit_pub`t'"
	use ${nc_raw_data}/`f'  /*in 1/100*/ , clear

	gen schoolyr = `t'

	append using ${nc_raw_data}/temp4
	save ${nc_raw_data}/temp4, replace
	tab schoolyr, m
}

drop if blank==1
drop blank

sort mastid schoolyr

gen g = real(grade)
replace g = 0 if grade=="0K"
replace g = -1 if grade=="PK"

replace ethnic = "A" if ethnic=="2"
replace ethnic = "B" if ethnic=="4"
replace ethnic = "H" if ethnic=="3"
replace ethnic = "I" if ethnic=="1"
replace ethnic = "M" if ethnic=="6"
replace ethnic = "W" if ethnic=="5"

gen i_sm = (sex=="M")
replace i_sm = . if sex=="NULL" | sex=="" | sex=="x"

gen i_bl = ethnic=="B"
gen i_wh = ethnic=="W"
gen i_hi = ethnic=="H"
gen i_as = ethnic=="A"
gen i_mr = ethnic=="M"
gen i_oth = ethnic!="B" & ethnic!="W"

gen i_eds = eds=="Y"
replace i_eds=. if eds=="" | eds=="NULL"

foreach x in bl wh hi as mr oth {
	replace i_`x' = . if ethnic=="NULL" | ethnic=="null" | ethnic=="" | ethnic=="x"
}

gen i_ec = (ec!="" & ec!="NULL" & ec!="N" & ec!="xx" & ec!="null")
gen i_grad = (graduate=="Y")
gen i_drop = (dropout=="Y")

gen i_lep = (lep=="Y" | lep=="3")
gen i_exlep = (lep=="2" | lep=="U")
gen i_everlep = (lep=="2" | lep=="U" | lep=="Y" | lep=="3")
replace i_lep = . if lep=="" | lep =="NULL" | lep=="null" | lep=="x"
replace i_exlep = . if lep=="" | lep =="NULL" | lep=="null" | lep=="x"
replace i_everlep = . if lep=="" | lep =="NULL" | lep=="null" | lep=="x"

gen i_xfer = XFER_CONFIRMED=="Y" | xfer_confirmed=="Y"
gen xfer_schlcode = XFER_SCHOOL_CODE
replace xfer_schlcode = xfer_school_code if xfer_schlcode==""

replace init_year = INIT_YEAR if init_year=="" | init_year=="NULL" | init_year=="NU" | init_year=="xx"
replace init_grade = INIT_GRADE if init_grade=="" | init_grade=="NULL" | init_grade=="NU" | init_grade=="xx"
replace last_year = LAST_YEAR if last_year=="" | last_year=="NULL" | last_year=="NU" | last_year=="xx"
replace grad_year = GRAD_YEAR if grad_year=="" | grad_year=="NULL" | grad_year=="NU" | grad_year=="xxxx"
replace dropout_year = DROPOUT_YEAR if dropout_year=="" | dropout_year=="NULL" | dropout_year=="NU" | dropout_year=="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
replace effective_exit_code = EFFECTIVE_EXIT_CODE if effective_exit_code==""


destring grad_year, force replace
destring dropout_year, force replace
destring init_year, force replace

rename grad_year grad_yr
rename dropout_year drop_yr
rename init_year init_yr

gen init_g = real(init_grade)
replace init_g = 0 if grade=="0K"
replace init_g = -1 if grade=="PK"

gen i_exitxfer = (effective_exit_code=="1" | effective_exit_code=="2" | effective_exit_code=="3" | effective_exit_code=="A" | effective_exit_code=="B" | effective_exit_code=="C" | effective_exit_code=="D" | effective_exit_code=="E" |effective_exit_code=="G")
gen i_exitdrop = effective_exit_code=="4"
gen i_exitdeath = effective_exit_code=="6"
gen i_exitgrad = effective_exit_code=="9"
gen i_exitunk = effective_exit_code=="?"
foreach x in xfer drop death grad unk {
	replace i_exit`x' = . if effective_exit_code=="X" | effective_exit_code==""
}

keep mastid schoolyr lea schlcode i_* *_yr xfer_schlcode init_g effective_exit_code ethnic eds
order mastid schoolyr lea schlcode i_* *_yr xfer_schlcode init_g effective_exit_code ethnic eds

save ${nc_clean_data}/exit_pub, replace
drop if mastid==.
gen one=1
collapse (count) n=one (max) i_*grad i_*drop  i_ec i_*death i_eds i_everlep i_exlep i_*xfer max_schoolyr= schoolyr max_g=g drop_yr grad_yr  ///
			(sum) n_xfer = i_xfer (min) init_yr init_g  min_schoolyr=schoolyr min_g=g ///
			, by(mastid)

save ${nc_clean_data}/exit_pub_1ob, replace

erase ${nc_raw_data}/temp4.dta
